<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Trigger Procedures</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/pgSQL - SQL Procedural Language"
HREF="plpgsql.html"><LINK
REL="PREVIOUS"
TITLE="Errors and Messages"
HREF="plpgsql-errors-and-messages.html"><LINK
REL="NEXT"
TITLE="PL/pgSQL Under the Hood"
HREF="plpgsql-implementation.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Errors and Messages"
HREF="plpgsql-errors-and-messages.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 39. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="PL/pgSQL Under the Hood"
HREF="plpgsql-implementation.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-TRIGGER"
>39.9. Trigger Procedures</A
></H1
><P
>    <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> can be used to define trigger
    procedures. A trigger procedure is created with the
    <TT
CLASS="COMMAND"
>CREATE FUNCTION</TT
> command, declaring it as a function with
    no arguments and a return type of <TT
CLASS="TYPE"
>trigger</TT
>.  Note that
    the function must be declared with no arguments even if it expects
    to receive arguments specified in <TT
CLASS="COMMAND"
>CREATE TRIGGER</TT
> &mdash;
    trigger arguments are passed via <TT
CLASS="VARNAME"
>TG_ARGV</TT
>, as described
    below.
  </P
><P
>   When a <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> function is called as a
   trigger, several special variables are created automatically in the
   top-level block. They are:

   <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="VARNAME"
>NEW</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>RECORD</TT
>; variable holding the new
       database row for <TT
CLASS="COMMAND"
>INSERT</TT
>/<TT
CLASS="COMMAND"
>UPDATE</TT
> operations in row-level
       triggers. This variable is <TT
CLASS="SYMBOL"
>NULL</TT
> in statement-level triggers
       and for <TT
CLASS="COMMAND"
>DELETE</TT
> operations.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>OLD</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>RECORD</TT
>; variable holding the old
       database row for <TT
CLASS="COMMAND"
>UPDATE</TT
>/<TT
CLASS="COMMAND"
>DELETE</TT
> operations in row-level
       triggers. This variable is <TT
CLASS="SYMBOL"
>NULL</TT
> in statement-level triggers
       and for <TT
CLASS="COMMAND"
>INSERT</TT
> operations.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_NAME</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>name</TT
>; variable that contains the name of the trigger actually
       fired.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_WHEN</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>text</TT
>; a string of
       <TT
CLASS="LITERAL"
>BEFORE</TT
>, <TT
CLASS="LITERAL"
>AFTER</TT
>, or
       <TT
CLASS="LITERAL"
>INSTEAD OF</TT
>, depending on the trigger's definition.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_LEVEL</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>text</TT
>; a string of either
       <TT
CLASS="LITERAL"
>ROW</TT
> or <TT
CLASS="LITERAL"
>STATEMENT</TT
>
       depending on the trigger's definition.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_OP</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>text</TT
>; a string of
       <TT
CLASS="LITERAL"
>INSERT</TT
>, <TT
CLASS="LITERAL"
>UPDATE</TT
>,
       <TT
CLASS="LITERAL"
>DELETE</TT
>, or <TT
CLASS="LITERAL"
>TRUNCATE</TT
>
       telling for which operation the trigger was fired.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_RELID</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>oid</TT
>; the object ID of the table that caused the
       trigger invocation.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_RELNAME</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>name</TT
>; the name of the table that caused the trigger
       invocation. This is now deprecated, and could disappear in a future
       release. Use <TT
CLASS="LITERAL"
>TG_TABLE_NAME</TT
> instead.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_TABLE_NAME</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>name</TT
>; the name of the table that
       caused the trigger invocation.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_TABLE_SCHEMA</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>name</TT
>; the name of the schema of the
       table that caused the trigger invocation.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_NARGS</TT
></DT
><DD
><P
>       Data type <TT
CLASS="TYPE"
>integer</TT
>; the number of arguments given to the trigger
       procedure in the <TT
CLASS="COMMAND"
>CREATE TRIGGER</TT
> statement.
      </P
></DD
><DT
><TT
CLASS="VARNAME"
>TG_ARGV[]</TT
></DT
><DD
><P
>       Data type array of <TT
CLASS="TYPE"
>text</TT
>; the arguments from
       the <TT
CLASS="COMMAND"
>CREATE TRIGGER</TT
> statement.
       The index counts from 0. Invalid
       indexes (less than 0 or greater than or equal to <TT
CLASS="VARNAME"
>tg_nargs</TT
>)
       result in a null value.
      </P
></DD
></DL
></DIV
><P>
  </P
><P
>    A trigger function must return either <TT
CLASS="SYMBOL"
>NULL</TT
> or a
    record/row value having exactly the structure of the table the
    trigger was fired for.
   </P
><P
>    Row-level triggers fired <TT
CLASS="LITERAL"
>BEFORE</TT
> can return null to signal the
    trigger manager to skip the rest of the operation for this row
    (i.e., subsequent triggers are not fired, and the
    <TT
CLASS="COMMAND"
>INSERT</TT
>/<TT
CLASS="COMMAND"
>UPDATE</TT
>/<TT
CLASS="COMMAND"
>DELETE</TT
> does not occur
    for this row).  If a nonnull
    value is returned then the operation proceeds with that row value.
    Returning a row value different from the original value
    of <TT
CLASS="VARNAME"
>NEW</TT
> alters the row that will be inserted or
    updated.  Thus, if the trigger function wants the triggering
    action to succeed normally without altering the row
    value, <TT
CLASS="VARNAME"
>NEW</TT
> (or a value equal thereto) has to be
    returned.  To alter the row to be stored, it is possible to
    replace single values directly in <TT
CLASS="VARNAME"
>NEW</TT
> and return the
    modified <TT
CLASS="VARNAME"
>NEW</TT
>, or to build a complete new record/row to
    return.  In the case of a before-trigger
    on <TT
CLASS="COMMAND"
>DELETE</TT
>, the returned value has no direct
    effect, but it has to be nonnull to allow the trigger action to
    proceed.  Note that <TT
CLASS="VARNAME"
>NEW</TT
> is null
    in <TT
CLASS="COMMAND"
>DELETE</TT
> triggers, so returning that is
    usually not sensible.  The usual idiom in <TT
CLASS="COMMAND"
>DELETE</TT
>
    triggers is to return <TT
CLASS="VARNAME"
>OLD</TT
>.
   </P
><P
>    <TT
CLASS="LITERAL"
>INSTEAD OF</TT
> triggers (which are always row-level triggers,
    and may only be used on views) can return null to signal that they did
    not perform any updates, and that the rest of the operation for this
    row should be skipped (i.e., subsequent triggers are not fired, and the
    row is not counted in the rows-affected status for the surrounding
    <TT
CLASS="COMMAND"
>INSERT</TT
>/<TT
CLASS="COMMAND"
>UPDATE</TT
>/<TT
CLASS="COMMAND"
>DELETE</TT
>).
    Otherwise a nonnull value should be returned, to signal
    that the trigger performed the requested operation. For
    <TT
CLASS="COMMAND"
>INSERT</TT
> and <TT
CLASS="COMMAND"
>UPDATE</TT
> operations, the return value
    should be <TT
CLASS="VARNAME"
>NEW</TT
>, which the trigger function may modify to
    support <TT
CLASS="COMMAND"
>INSERT RETURNING</TT
> and <TT
CLASS="COMMAND"
>UPDATE RETURNING</TT
>
    (this will also affect the row value passed to any subsequent triggers).
    For <TT
CLASS="COMMAND"
>DELETE</TT
> operations, the return value should be
    <TT
CLASS="VARNAME"
>OLD</TT
>.
   </P
><P
>    The return value of a row-level trigger
    fired <TT
CLASS="LITERAL"
>AFTER</TT
> or a statement-level trigger
    fired <TT
CLASS="LITERAL"
>BEFORE</TT
> or <TT
CLASS="LITERAL"
>AFTER</TT
> is
    always ignored; it might as well be null. However, any of these types of
    triggers might still abort the entire operation by raising an error.
   </P
><P
>    <A
HREF="plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE"
>Example 39-3</A
> shows an example of a
    trigger procedure in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>.
   </P
><DIV
CLASS="EXAMPLE"
><A
NAME="PLPGSQL-TRIGGER-EXAMPLE"
></A
><P
><B
>Example 39-3. A <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Trigger Procedure</B
></P
><P
>     This example trigger ensures that any time a row is inserted or updated
     in the table, the current user name and time are stamped into the
     row. And it checks that an employee's name is given and that the
     salary is a positive value.
    </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary &lt; 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();</PRE
></DIV
><P
>    Another way to log changes to a table involves creating a new table that
    holds a row for each insert, update, or delete that occurs. This approach
    can be thought of as auditing changes to a table.
    <A
HREF="plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE"
>Example 39-4</A
> shows an example of an
    audit trigger procedure in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>.
   </P
><DIV
CLASS="EXAMPLE"
><A
NAME="PLPGSQL-TRIGGER-AUDIT-EXAMPLE"
></A
><P
><B
>Example 39-4. A <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Trigger Procedure For Auditing</B
></P
><P
>     This example trigger ensures that any insert, update or delete of a row
     in the <TT
CLASS="LITERAL"
>emp</TT
> table is recorded (i.e., audited) in the <TT
CLASS="LITERAL"
>emp_audit</TT
> table.
     The current time and user name are stamped into the row, together with
     the type of operation performed on it.
    </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();</PRE
></DIV
><P
>    A variation of the previous example uses a view joining the main table
    to the audit table, to show when each entry was last modified. This
    approach still records the full audit trail of changes to the table,
    but also presents a simplified view of the audit trail, showing just
    the last modified timestamp derived from the audit trail for each entry.
    <A
HREF="plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE"
>Example 39-5</A
> shows an example
    of an audit trigger on a view in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
>.
   </P
><DIV
CLASS="EXAMPLE"
><A
NAME="PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE"
></A
><P
><B
>Example 39-5. A <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> View Trigger Procedure For Auditing</B
></P
><P
>     This example uses a trigger on the view to make it updatable, and
     ensure that any insert, update or delete of a row in the view is
     recorded (i.e., audited) in the <TT
CLASS="LITERAL"
>emp_audit</TT
> table. The current time
     and user name are recorded, together with the type of operation
     performed, and the view displays the last modified time of each row.
    </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE emp (
    empname           text PRIMARY KEY,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Perform the required operation on emp, and create a row in emp_audit
        -- to reflect the change made to emp.
        --
        IF (TG_OP = 'DELETE') THEN
            DELETE FROM emp WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            OLD.last_updated = now();
            INSERT INTO emp_audit VALUES('D', user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('U', user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('I', user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE PROCEDURE update_emp_view();</PRE
></DIV
><P
>    One use of triggers is to maintain a summary table
    of another table. The resulting summary can be used in place of the
    original table for certain queries &mdash; often with vastly reduced run
    times.
    This technique is commonly used in Data Warehousing, where the tables
    of measured or observed data (called fact tables) might be extremely large.
    <A
HREF="plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE"
>Example 39-6</A
> shows an example of a
    trigger procedure in <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> that maintains
    a summary table for a fact table in a data warehouse.
   </P
><DIV
CLASS="EXAMPLE"
><A
NAME="PLPGSQL-TRIGGER-SUMMARY-EXAMPLE"
></A
><P
><B
>Example 39-6. A <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Trigger Procedure For Maintaining A Summary Table</B
></P
><P
>     The schema detailed here is partly based on the <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Grocery Store
     </I
></SPAN
> example from <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>The Data Warehouse Toolkit</I
></SPAN
>
     by Ralph Kimball.
    </P
><PRE
CLASS="PROGRAMLISTING"
>--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -&gt; % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Insert or update the summary row with the new values.
        &lt;&lt;insert_update&gt;&gt;
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;</PRE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-errors-and-messages.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-implementation.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Errors and Messages</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> Under the Hood</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>